/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.MenuItemDao;
import com.feib.stms.dao.jdbc.rowmapper.MenuItemRowMapper;
import com.feib.stms.model.MenuItem;
import com.feib.stms.model.Role;
import com.feib.stms.pagination.PaginatedList;
import com.feib.stms.util.UserUtil;
/**
 * @author Jimmy Liu
 *
 */
@Repository("menuItemDao")
public class MenuItemJdbcDao extends BaseJdbcDao implements MenuItemDao 
{
	private OracleSequenceMaxValueIncrementer sequence;
	
	private SimpleJdbcInsert menuItemJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "MENU_ITEM_PK_SEQ");
		this.menuItemJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.menuItemJdbcInsert.withTableName("MENU_ITEM")
			.usingColumns("MENU_ITEM_PK", "ACTION_BEAN_CLASS", "ACTION_NAME", "MENU_NO", "PARENT_MENU_ITEM_PK"
				, "ACTION_PAGE", "IS_ENABLED", "IS_SECURITIES", "ORDER_NO", "IMG_ID"
				, "UPDATED_DATE", "UPDATED_BY" ,"CREATED_DATE" ,"CREATED_BY");
	}
	
	public MenuItem getByMenuItemPk(Long menuItemPk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT M.*");
		sql.append(" FROM MENU_ITEM M");
		sql.append(" WHERE M.MENU_ITEM_PK = :MENU_ITEM_PK");
		parameters.addValue("MENU_ITEM_PK", menuItemPk, Types.NUMERIC);
		return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new MenuItemRowMapper(this), parameters);
	}

	public List<MenuItem> getByRole(Role role) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT M.*");
		sql.append(" , R.GROUP_PK, R.ROLE_NO, R.ROLE_NAME");
		sql.append(" , G.GROUP_NO, G.GROUP_NAME");
		sql.append(" FROM APP_ROLE_MENU RM");
		sql.append(" , APP_ROLE R, MENU_ITEM M, APP_GROUP G ");
		sql.append(" WHERE RM.MENU_ITEM_PK = M.MENU_ITEM_PK");
		sql.append(" AND RM.ROLE_PK = R.ROLE_PK");
		sql.append(" AND R.GROUP_PK = G.GROUP_PK");
		sql.append(" AND RM.ROLE_PK = :ROLE_PK");
		parameters.addValue("ROLE_PK",  role.getRolePk(), Types.NUMERIC);
		return this.getSimpleJdbcTemplate().query(sql.toString(), new MenuItemRowMapper(this), parameters);
	}
	
	public List<MenuItem> getAllEnabledMenuItems() {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT M.*");
		sql.append(" FROM MENU_ITEM M WHERE M.IS_ENABLED = 1");
		sql.append(" ORDER BY ORDER_NO, MENU_NO");
		return this.getSimpleJdbcTemplate().query(sql.toString(), new MenuItemRowMapper(this), parameters);
	}
	
	public List<MenuItem> getAllEnabledMenuItemsForSecurities() {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("SELECT M.*");
		sql.append(" FROM MENU_ITEM M WHERE M.IS_ENABLED = 1 AND M.IS_SECURITIES = 1 ");
		sql.append(" ORDER BY ORDER_NO, MENU_NO");
		return this.getSimpleJdbcTemplate().query(sql.toString(), new MenuItemRowMapper(this), parameters);
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#findPaginatedListByMenuItem(java.lang.String, java.lang.String, java.lang.String)
	 */
	public PaginatedList<MenuItem> queryForPaginatedListByMenuItem(MenuItem menuItem , String menuType, PaginatedList paginatedList) {
		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderByMenuItem(menuItem, menuType, paginatedList);		
		return this.queryForPaginatedList(sqlQueryProvider, new MenuItemRowMapper(this), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}
	/*
	 * 拼接分頁查詢sql
	 */
	protected SqlQueryProvider getSqlQueryProviderByMenuItem(MenuItem menuItem ,String menuType , PaginatedList paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		
		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();
		
		sql.append("SELECT *");
		sql.append(" FROM MENU_ITEM WHERE 1 = 1");
		if (null != menuItem)
		{	
			/* 程式代碼 */
			if(!StringUtils.isEmpty(menuItem.getMenuNo())){
				sql.append(" AND MENU_NO = ?");
				args.add(menuItem.getMenuNo());
			}
			/* 程式名稱 */
			if(!StringUtils.isEmpty(menuItem.getActionName())){
				sql.append(" AND ACTION_NAME LIKE ?");
				args.add("%" + menuItem.getActionName() + "%");
			}
			/* 若程式類別為程式群組，則上層程式為null，若程式類別為程式功能，則上層程式不為null */
			if(!StringUtils.isEmpty(menuType)){
				if("0".equals(menuType)){//程式群組
					sql.append(" AND PARENT_MENU_ITEM_PK IS NULL");	
				}else if("1".equals(menuType)){//程式功能
					sql.append(" AND PARENT_MENU_ITEM_PK IS NOT NULL");
				}
			}				
		}
		/* 排序 */
		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		return sqlQueryProvider;
	}
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#initMenuItemOptionList()
	 */
	public List<MenuItem> initMenuItemOptionList() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT M.*");
		sql.append(" FROM MENU_ITEM M");
		sql.append(" WHERE M.PARENT_MENU_ITEM_PK IS NULL");
		sql.append(" ORDER BY M.MENU_ITEM_PK");
		return this.getSimpleJdbcTemplate().query(sql.toString(), new MenuItemRowMapper(this));
	}
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#createMenuItem(com.feib.stms.model.MenuItem)
	 */
	public int insert(MenuItem menuItem) throws DataAccessException{
		int insertCounts = 0;
		try{
			if(menuItem != null){
				String userId = UserUtil.getUserId();
				menuItem.setUpdatedBy(userId);
				menuItem.setCreatedBy(userId);
			}
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			Long menuItemPk = new Long(this.sequence.nextLongValue());
			parameters.addValue("MENU_ITEM_PK",  menuItemPk, Types.NUMERIC);
			parameters.addValue("ACTION_BEAN_CLASS",  menuItem.getActionBeanClass(), Types.VARCHAR);
			parameters.addValue("ACTION_NAME",  menuItem.getActionName(), Types.VARCHAR);
			parameters.addValue("MENU_NO", menuItem.getMenuNo(), Types.VARCHAR);
			if(menuItem.getParentMenuItem() != null){
				parameters.addValue("PARENT_MENU_ITEM_PK", menuItem.getParentMenuItem().getMenuItemPk(), Types.NUMERIC);
			}else{
				parameters.addValue("PARENT_MENU_ITEM_PK",  null, Types.NUMERIC);
			}
			parameters.addValue("ACTION_PAGE",  menuItem.getActionPage(), Types.VARCHAR);
			parameters.addValue("IS_ENABLED",  convertToInteger(menuItem.getEnabled()), Types.NUMERIC);
			parameters.addValue("IS_SECURITIES",  convertToInteger(menuItem.getSecurities()) , Types.NUMERIC);
			parameters.addValue("ORDER_NO",  menuItem.getOrderNo(), Types.VARCHAR);
			parameters.addValue("IMG_ID",  menuItem.getImgId(), Types.VARCHAR);
			parameters.addValue("UPDATED_DATE",  menuItem.getUpdatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_BY",  menuItem.getUpdatedBy(), Types.VARCHAR);
			parameters.addValue("CREATED_DATE",  menuItem.getCreatedDate(), Types.TIMESTAMP);
			parameters.addValue("CREATED_BY",  menuItem.getCreatedBy(), Types.VARCHAR);
			insertCounts = menuItemJdbcInsert.execute(parameters);
			if (insertCounts > 0)
				menuItem.setMenuItemPk(menuItemPk);
			return insertCounts;
		}
		catch (DataAccessException e) {
			logger.error(e.getMessage(), e);
		}
		return 0;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#deleteMenuItem(com.feib.stms.model.MenuItem)
	 */
	public int delete(Long pk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM MENU_ITEM WHERE MENU_ITEM_PK = :MENU_ITEM_PK");
		parameters.addValue("MENU_ITEM_PK", pk, Types.NUMERIC);
		int updateCount = this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
		return updateCount;
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#updateMenuItem(com.feib.stms.model.MenuItem)
	 */
	public int update(MenuItem menuItem) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("UPDATE MENU_ITEM SET MENU_NO = :MENU_NO,ACTION_BEAN_CLASS = :ACTION_BEAN_CLASS,ACTION_NAME = :ACTION_NAME ");
		sql.append(",PARENT_MENU_ITEM_PK = :PARENT_MENU_ITEM_PK,ACTION_PAGE = :ACTION_PAGE,IS_ENABLED = :IS_ENABLED,IS_SECURITIES = :IS_SECURITIES ");
		sql.append(",ORDER_NO = :ORDER_NO,IMG_ID = :IMG_ID,UPDATED_DATE = :UPDATED_DATE,UPDATED_BY = :UPDATED_BY ");
		sql.append(" WHERE MENU_ITEM_PK = :MENU_ITEM_PK");
		parameters.addValue("MENU_NO",  menuItem.getMenuNo(), Types.VARCHAR);
		if(menuItem.getParentMenuItem() != null){
			parameters.addValue("PARENT_MENU_ITEM_PK",menuItem.getParentMenuItem().getMenuItemPk(), Types.NUMERIC);
		}else{
			parameters.addValue("PARENT_MENU_ITEM_PK", null, Types.NUMERIC);
		}
		parameters.addValue("ACTION_BEAN_CLASS",  menuItem.getActionBeanClass(), Types.VARCHAR);
		parameters.addValue("ACTION_NAME",  menuItem.getActionName(), Types.VARCHAR);
		parameters.addValue("ACTION_PAGE", menuItem.getActionPage(), Types.VARCHAR);
		parameters.addValue("IS_ENABLED",  convertToInteger(menuItem.getEnabled()), Types.NUMERIC);
		parameters.addValue("IS_SECURITIES",  convertToInteger(menuItem.getSecurities()), Types.NUMERIC);
		parameters.addValue("ORDER_NO",  menuItem.getOrderNo(), Types.VARCHAR);
		parameters.addValue("IMG_ID",  menuItem.getImgId(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  menuItem.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY", menuItem.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("MENU_ITEM_PK",  menuItem.getMenuItemPk(), Types.NUMERIC);
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#countParentMenuItem(java.lang.Long)
	 */
	public int countParentMenuItem(Long pk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM MENU_ITEM WHERE PARENT_MENU_ITEM_PK = :MENU_ITEM_PK");
		parameters.addValue("MENU_ITEM_PK", pk, Types.NUMERIC);
		int updateCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return updateCount;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.MenuItemDao#getSecuritiesMenuItem()
	 */
	public List<MenuItem> getSecuritiesMenuItem() {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		
		sql.append("SELECT M.*");
		sql.append(" FROM MENU_ITEM M WHERE M.IS_SECURITIES = 1");
		sql.append(" ORDER BY ORDER_NO, MENU_NO");
		return this.getSimpleJdbcTemplate().query(sql.toString(), new MenuItemRowMapper(this), parameters);
	}
}
